﻿using Meiam.System.Interfaces.IService;
using Meiam.System.Model.Entity;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Meiam.System.Interfaces
{
    public class YieldSummaryService:SqlSugarBase<YieldSummary>, IYieldSummaryService
    {
        public YieldSummaryService(IConfiguration _configuration)
        {
            base.connectionString = _configuration.GetConnectionString("DB1");
        }

       


        /// <summary>
        /// 良率汇总
        /// </summary>
        /// <returns></returns>
        public List<YieldSummary> GetYieldSummaryData(string shift,string start, string end)
        {
            string startTime = shift=="晚班"?DateTime.Now.AddDays(-1).ToShortDateString(): DateTime.Now.ToShortDateString() + " " + start+":00";
            string endTime = DateTime.Now.ToShortDateString()+" "+end + ":00";

            string sql = @"
with abc as 
(
select wlddh.wipdatavalue,
       p.cusize,
       P.CUTHICKNESS,
       sum(altb.Curealqty) as cupiececount,
       sum(nvl(altb.cupiececount, 0)) Curealqty,
       sum(altb.Curealqty - altb.cupiececount)  as count,
       case
         when sum(altb.Curealqty) is null or sum(altb.Curealqty) = 0 then
          0
         else
          round(sum(altb.cupiececount) / sum(altb.curealqty) * 100, 2)
       end || '%' as 进片数比例,
       to_char(round(((sum(nvl(CU.cuAPlusGrader, 0)) +
                     sum(nvl(CU.cuAPlusGrader1, 0)) +
                     sum(nvl(CU.cuAPlusGrader3, 0))) /
                     sum（altb.cupiececount) * 100),
               2),
       'fm999990.00') || '%' as " + "\"A+(汇总)\"," +
       @"case when sum(nvl(CU.cuAPlusGrader, 0)) / sum(altb.cupiececount) is not null then to_char(round(sum(nvl(CU.cuAPlusGrader, 0)) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"A+等外\"," +
          "case when sum(CU.cuAPlusGrader1) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuAPlusGrader1) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"A+(0.4-1.1)\"," +
            "case when sum(CU.cuAPlusGrader2) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuAPlusGrader2) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"A-(1.1-1.8)\"," +
              "case when sum(CU.cuAPlusGrader3) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuAPlusGrader3) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"A+(13-15线痕)\"," +
                "case when sum(CU.cuAGraderWp) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuAGraderWp) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"A-污片\"," +
                  "case when sum(CU.cuAGraderBb) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuAGraderBb) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"A-崩边\"," +
                     "case when sum(CU.cuAGraderXh) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuAGraderXh) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"A-线痕\"," +
                       "case when sum(CU.cuAGraderQq) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuAGraderQq) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"A-翘曲\"," +
                       "case when sum(CU.cuAGrader) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuAGrader) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"A+(0.2-0.4)\"," +
                         "case when sum(nvl(CU.cuAGraderHP, 0)) / sum(altb.cupiececount) is not null then to_char(round(sum(nvl(CU.cuAGraderHP, 0)) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"A-花片\"," +
                            "case when sum(CU.cuBGraderGl) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuBGraderGl) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"B硅落\"," +
                               "case when sum(CU.cuBGraderXh) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuBGraderXh) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"B线痕\"," +
                                  "case when sum(CU.cuBGraderQq) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuBGraderQq) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"B翘曲\"," +
                                    "case when sum(CU.cuBGraderHb) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuBGraderHb) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"B厚薄\"," +
                                      "case when sum(CU.cuBGraderTtv) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuBGraderTtv) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"BTTV\"," +
                                        "case when sum(CU.cuBGraderDz) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuBGraderDz) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"B电阻\"," +
       "case when sum(CU.cuBGraderCc) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuBGraderCc) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"B尺寸\"," +
          "case when sum(nvl(CU.cuBGraderHP, 0)) / sum(altb.cupiececount) is not null then to_char(round(sum(nvl(CU.cuBGraderHP, 0)) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"B花片\"," +
             "case when sum(CU.cuCGraderBg) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuCGraderBg) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"C崩硅\"," +
               "case when sum(CU.cuCGraderCc) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuCGraderCc) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"C尺寸\"," +
                 "case when sum(CU.cuDGraderCk) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuDGraderCk) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"D穿孔\"," +
                   "case when sum(CU.cuDGraderYl) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuDGraderYl) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"D隐裂\"," +
                     "case when sum(nvl(CU.cucgradedianzu, 0)) / sum(altb.cupiececount) is not null then to_char(round(sum(nvl(CU.cucgradedianzu, 0)) / sum(nvl(altb.cupiececount, 0)) * 100, 2), 'fm999990.00') || '%' end " + "\"C电阻\"," +
                       "case when sum(nvl(CU.cuDGraderPP, 0)) / sum(altb.cupiececount) is not null then to_char(round(sum(nvl(CU.cuDGraderPP, 0)) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"D破片\"," +
                         "case when sum(CU.cuGraderQj) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuGraderQj) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"D缺角\"," +
                         "case when sum(nvl(CU.cuGraderDD, 0)) / sum(altb.cupiececount) is not null then to_char(round(sum(nvl(CU.cuGraderDD, 0)) / sum(nvl(altb.cupiececount, 0)) * 100, 2), 'fm999990.00') || '%' end " + "\"待判定\"," +
                           "case when sum(CU.cuUnKnowGrader) / sum(altb.cupiececount) is not null then to_char(round(sum(CU.cuUnKnowGrader) / sum(altb.cupiececount) * 100, 2), 'fm999990.00') || '%' end " + "\"未测到\" " +
  @" from container C
 INNER JOIN a_wiplothistory wlh
    ON C.CONTAINERID = wlh.Containerid
   and wlh.specname = N'切片'
  left join a_wiplotdetailshistory wldh
    on wlh.wiplothistoryid = wldh.wiplothistoryid
  left join a_wiplotdetailsdatahistory wlddh
    on wldh.wiplotdetailshistoryid = wlddh.wiplotdetailshistoryid
   and wlddh.wipdatanamename = 'WorkCenter'
 INNER JOIN a_lotattributes altb
    ON altb.containerid = c.containerid
  LEFT join a_wiplothistory QX
    on QX.Containerid = c.containerid
   and QX.specname = N'清洗'
  LEFT join a_wiplothistory FX
    on FX.Containerid = c.containerid
   and FX.SPECNAME =N'分选'
 INNER JOIN PRODUCT P
    ON P.PRODUCTID = C.PRODUCTID
 inner join historymainline hi
    on hi.containerid = c.containerid
   and hi.specname = N'分选'
   and hi.cdoname = 'MoveLot'
  left join cuContainerGraderRecord CU
    ON CU.CONTAINERNAME = C.CONTAINERNAME
 where altb.Curealqty > 0
   AND altb.cupiececount > 0
    and hi.SYSTEMDATE >= to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') and hi.SYSTEMDATE <= to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
 group by wlddh.wipdatavalue, p.cusize, P.CUTHICKNESS, CU.cuAPlusGrader
 order by wlddh.wipdatavalue, p.cusize, P.CUTHICKNESS desc
)
select wipdatavalue as WorkShop,
cusize as CuSize,
CUTHICKNESS as Thickness,
good888 as Type , 
good666 as Proportion 
from abc
unpivot
((good666) for good888 in (
     进片数比例,"+
    "\"A+(汇总)\","+
    "\"A+(0.4-1.1)\","+
    "\"A-(1.1-1.8)\","+
    "\"A+(13-15线痕)\","+
    "\"A-污片\"," +
    "\"A-崩边\"," +
    "\"A-线痕\"," +
    "\"A-翘曲\"," +
    "\"A+(0.2-0.4)\","+
    "\"A-花片\"," +
    "\"B硅落\"," +
    "\"B线痕\"," +
    "\"B翘曲\"," +
    "\"B厚薄\"," +
    "\"BTTV\"," +
    "\"B电阻\"," +
    "\"B尺寸\"," +
    "\"B花片\"," +
    "\"C崩硅\"," +
    "\"C电阻\"," +
    "\"C尺寸\"," +
    "\"D穿孔\"," +
    "\"D隐裂\"," +
    "\"D缺角\"," +
    "\"D破片\"," +
    "\"未测到\"," +
    "\"待判定\"" +
    @"
     )
)
";   
            return base.Query(sql);
        }
    }


}
